package ext.csc.component.numbergen.number;

import java.io.Serializable;
import java.sql.ResultSet;
import java.util.ArrayList;

import wt.method.RemoteAccess;
import wt.method.RemoteMethodServer;

import ext.csc.component.numbergen.bean.RequestInfoContained;
import ext.csc.component.numbergen.connection.DBConn;

public class NumberRegisterManager implements RemoteAccess, Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 7051284998306004922L;

	/**
	public void cancelOneNumber(String number, String desc) throws Exception{
		try {
			if (!RemoteMethodServer.ServerFlag) {
				RemoteMethodServer.getDefault().invoke("cancelOneNumberRemote", NumberRegisterManager.class.getName(), null,
						new Class[] {String.class, String.class},
						new Object[] {number, desc});
			} else {
				cancelOneNumberRemote(number, desc);
			}
		} catch (Exception e) {
			throw e;
		}
	}**/
	
	public void cancelOneNumber(String number, String desc) throws Exception{
		DBConn conn = null;
		try{
			conn = new DBConn();
			
			String sql;
			sql = "SELECT * FROM csc_generatednumber WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "'"; 
			ResultSet rs = conn.executeQuery(sql);
			
			if(rs.next()){
				sql = "UPDATE csc_generatednumber SET " + 
						GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + NumberManager.CANCELLED_FLAG +  ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + " = '" + desc + "'" + 
						" WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "' ";
			}

			conn.executeUpdate(sql);
			conn.commit();
		}catch(Exception ex){
			ex.printStackTrace();
			conn.rollback();
			throw new Exception("*** Cancel Terminated!! ***  Cancel number: " + number + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception("*** Cancel Terminated!! ***  Cancel number: " + number + " Failed!");
			}
		}
	}
	
	//For Jiuzhou
	public void approveOneNumber(String number, String desc) throws Exception{
		DBConn conn = null;
		try{
			conn = new DBConn();
			
			String sql;
			sql = "SELECT * FROM csc_generatednumber WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "'"; 
			ResultSet rs = conn.executeQuery(sql);
			
			if(rs.next()){
				sql = "UPDATE csc_generatednumber SET " + 
						GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + NumberManager.APPROVED_FLAG +  ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC + " = '" + desc + "'" + 
						" WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "' ";
			}

			conn.executeUpdate(sql);
			conn.commit();
		}catch(Exception ex){
			ex.printStackTrace();
			conn.rollback();
			throw new Exception("*** Approve Terminated!! ***  Approve number: " + number + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception("*** Approve Terminated!! ***  Approve number: " + number + " Failed!");
			}
		}
	}
	/**
	public void registerOneNumber(NumberInfoContained gnumber, RequestInfoContained requestInfo) throws Exception{
		try {
			if (!RemoteMethodServer.ServerFlag) {
				RemoteMethodServer.getDefault().invoke("registerOneNumberRemote", NumberRegisterManager.class.getName(), null,
						new Class[] {NumberInfoContained.class, RequestInfoContained.class},
						new Object[] {gnumber, requestInfo});
			} else {
				registerOneNumberRemote(gnumber, requestInfo);
			}
		} catch (Exception e) {
			throw e;
		}
	}**/
	
	public void registerOneNumber(NumberInfoContained gnumber, RequestInfoContained requestInfo) throws Exception{
		System.out.println("Begin registerOneNumberRemote=======" );
		
		String gn_number = "'" + gnumber.getNumber() + "' ";
		String gn_class = "'" + gnumber.getClasspath() + "' ";
		String gn_requestor = "'" + requestInfo.getRequestor() + "'";
		String gn_requestdatetime = "to_date('" + requestInfo.getDatetime() + "','yyyy-mm-dd hh24:mi:ss') ";
		String gn_flag = gnumber.getFlag() + " ";
		String gn_requestdesc = "'" + requestInfo.getRequestdesc() + "' ";
		String gn_name = "'" + requestInfo.getObjectname() + "' ";
		String gn_canceldesc = "'" + requestInfo.getCanceldesc() + " ' ";
		String gn_seq = String.valueOf(gnumber.getSeq()) + " ";

		DBConn conn = null;
		
		try{
			conn = new DBConn();
			
			String sql;
			sql = "SELECT * FROM csc_generatednumber WHERE " + 
					GeneratedNumber.GENERATEDNUMBER_GN_CLASS + " = " + gn_class + " AND " + 
					GeneratedNumber.GENERATEDNUMBER_GN_SEQ + " = " + gn_seq; 
			
			System.out.println("registerOneNumberRemote=======" + sql);
			ResultSet rs = conn.executeQuery(sql);
			
			if(!rs.next()){
				sql = "INSERT INTO csc_generatednumber " + 
							"(" + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + "," + GeneratedNumber.GENERATEDNUMBER_GN_CLASS + "," + 
							GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR + "," + GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDATETIME + "," + 
							GeneratedNumber.GENERATEDNUMBER_GN_FLAG + "," + GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC + "," + 
							GeneratedNumber.GENERATEDNUMBER_GN_NAME + "," + GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + "," + 
							GeneratedNumber.GENERATEDNUMBER_GN_SEQ + ") " +
							"VALUES(" + gn_number + "," + gn_class + "," + gn_requestor + "," + gn_requestdatetime + "," + gn_flag + "," +
										gn_requestdesc + "," + gn_name + "," + gn_canceldesc + "," + gn_seq + ")";
			}else{
				sql = "UPDATE csc_generatednumber SET " + 
						GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = " + gn_number + ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_REQUESTOR + " = " + gn_requestor + ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDATETIME + " = " + gn_requestdatetime + ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + gn_flag +  ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_REQUESTDESC + " = " + gn_requestdesc +  ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_NAME + " = " + gn_name +  ", " + 
						GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + " = " + gn_canceldesc +  " " + 
						" WHERE " + 
						GeneratedNumber.GENERATEDNUMBER_GN_CLASS + " = " + gn_class + " AND " + 
						GeneratedNumber.GENERATEDNUMBER_GN_SEQ + " = " + gn_seq; 
			}
			
			conn.executeUpdate(sql);
			conn.commit();
		}catch(Exception ex){
			ex.printStackTrace();
			conn.rollback();
			throw new Exception("*** Register Terminated!! ***  Register number: " + gn_number + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void registerUsedNumber(String gn_number) throws Exception{
		gn_number = "'" + gn_number + "' ";
		
		DBConn conn = null;
		
		try{
			conn = new DBConn();
			
			String sql;
			sql = "UPDATE csc_generatednumber SET " + 
					GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = 2 " + 
					" WHERE " + 
					GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = " +  gn_number;

			conn.executeUpdate(sql);
			conn.commit();
		}catch(Exception ex){
			ex.printStackTrace();
			conn.rollback();
			throw new Exception("*** Set Used Flag Terminated!! *** number: " + gn_number + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public void cancelNumbers(ArrayList numbers, String desc) throws Exception{
		DBConn conn = null;
		String number = "";
		
		try{
			conn = new DBConn();
			
			String sql;
			
			for (int i = 0; i < numbers.size(); i++) {
				number = (String)numbers.get(i);
				
				sql = "SELECT * FROM csc_generatednumber WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "'"; 
				ResultSet rs = conn.executeQuery(sql);
				
				if(rs.next()){
					sql = "UPDATE csc_generatednumber SET " + 
							GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + NumberManager.CANCELLED_FLAG +  ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + " = '" + desc + "'" + 
							" WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "' ";
				}

				conn.executeUpdate(sql);
			}

			conn.commit();
		}catch(Exception ex){
			ex.printStackTrace();
			conn.rollback();
			throw new Exception("*** Cancel Terminated!! ***  Cancel number: " + number + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception("*** Cancel Terminated!! ***  Cancel number: " + number + " Failed!");
			}
		}
	}
	
	//For Jiuzhou
	public void approveNumbers(ArrayList numbers, String desc) throws Exception{
		DBConn conn = null;
		String number = "";
		
		try{
			conn = new DBConn();
			
			String sql;
			
			for (int i = 0; i < numbers.size(); i++) {
				number = (String)numbers.get(i);
				
				sql = "SELECT * FROM csc_generatednumber WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "'"; 
				ResultSet rs = conn.executeQuery(sql);
				
				if(rs.next()){
					sql = "UPDATE csc_generatednumber SET " + 
							GeneratedNumber.GENERATEDNUMBER_GN_FLAG + " = " + NumberManager.APPROVED_FLAG +  ", " + 
							GeneratedNumber.GENERATEDNUMBER_GN_CANCELDESC + " = '" + desc + "'" + 
							" WHERE " + GeneratedNumber.GENERATEDNUMBER_GN_NUMBER + " = '" + number + "' ";
				}
				
				System.out.println(sql);
				conn.executeUpdate(sql);
			}

			conn.commit();
		}catch(Exception ex){
			ex.printStackTrace();
			conn.rollback();
			throw new Exception("*** Approve Terminated!! ***  Approve number: " + number + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception("*** Approve Terminated!! ***  Approve number: " + number + " Failed!");
			}
		}
	}
}
